package com.ideal.manage.esms.service.report;

import com.alibaba.fastjson.JSONObject;
import com.ideal.manage.esms.bean.DTO.PageDto;
import com.ideal.manage.esms.bean.alarm.Alarm;
import com.ideal.manage.esms.bean.diagnostic.DiagnoseResultInfo;
import com.ideal.manage.esms.bean.diagnostic.QDiagnoseResultInfo;
import com.ideal.manage.esms.bean.esms.EsmsIntegration;
import com.ideal.manage.esms.bean.esms.QEsmsIntegration;
import com.ideal.manage.esms.bean.project.EquipmentManage;
import com.ideal.manage.esms.bean.project.ParameterInfo;
import com.ideal.manage.esms.bean.project.ProjectManage;
import com.ideal.manage.esms.bean.project.QEquipmentManage;
import com.ideal.manage.esms.bean.report.ReporTearnings;
import com.ideal.manage.esms.bean.report.ReportFaultInformation;
import com.ideal.manage.esms.bean.report.ReportPackageInconformity;
import com.ideal.manage.esms.jpa.JpaRepositoryImpl;
import com.ideal.manage.esms.repository.alarm.AlarmRepository;
import com.ideal.manage.esms.repository.diagnostic.DiagnoseResultInfoRepository;
import com.ideal.manage.esms.repository.framework.MySpecification;
import com.ideal.manage.esms.repository.framework.SpecificationOperator;
import com.ideal.manage.esms.repository.integration.IntegrationRepository;
import com.ideal.manage.esms.repository.programme.ParameterInfoRepository;
import com.ideal.manage.esms.repository.project.ProjectManageRepository;
import com.ideal.manage.esms.repository.report.ReportFaultInformationRepository;
import com.ideal.manage.esms.repository.report.ReportPackageInconformityRepository;
import com.ideal.manage.esms.repository.report.ReportRepository;
import com.ideal.manage.esms.repository.report.ReportTearingsRepository;
import com.ideal.manage.esms.util.DateBtnUtil;
import com.ideal.manage.esms.util.HttpRequests;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.servlet.http.HttpServletRequest;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author Allen
 * 年报数据处理业务
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class ReportYearService {
    private Logger LOG = LoggerFactory.getLogger(ReportYearService.class);

    @Autowired
    private EntityManager entityManager;
    @Autowired
    private ProjectManageRepository projectManageRepository;
    @Autowired
    private ParameterInfoRepository parameterInfoRepository;
    @Autowired
    private IntegrationRepository integrationRepository;
    @Autowired
    private AlarmRepository alarmRepository;
    @Autowired
    private ReportFaultInformationRepository reportFaultInformationRepository;
    @Autowired
    private DiagnoseResultInfoRepository diagnoseResultInfoRepository;
    @Autowired
    private ReportPackageInconformityRepository reportPackageInconformityRepository;
    @Autowired
    private ReportRepository reportRepository;
    @Autowired
    private ReportTearingsRepository reportTearingsRepository;

    private JpaRepositoryImpl<DiagnoseResultInfo, String> diagnoseResultInfoRepositorys;
    private JpaRepositoryImpl<EquipmentManage, String> equipmentManageRepository;
    private JpaRepositoryImpl<EsmsIntegration, Long> integrationRepositorys;
    private JpaRepositoryImpl<ProjectManage, String> projectManageRepositorys;

    @PostConstruct
    public void init() {
        this.diagnoseResultInfoRepositorys = new JpaRepositoryImpl<DiagnoseResultInfo, String>(DiagnoseResultInfo.class, entityManager);
        this.integrationRepositorys = new JpaRepositoryImpl<EsmsIntegration, Long>(EsmsIntegration.class, entityManager);
        this.equipmentManageRepository = new JpaRepositoryImpl<>(EquipmentManage.class, entityManager);
        this.projectManageRepositorys = new JpaRepositoryImpl<>(ProjectManage.class, entityManager);
    }


    /**
     * 电量统计信息
     *
     * @return
     */
    public Map<String, JSONObject> getBatteryLeftData(String id) throws ParseException {
        // 获取项目的ip
        ProjectManage one = projectManageRepository.findOne(id);
        // 找到配置参数测点中的项目级
        List<ParameterInfo> infos = parameterInfoRepository.findAllByProject(1);
        ParameterInfo ljcdl = null;
        ParameterInfo ljfdl = null;
        ParameterInfo ljjsdf = null;
        for (ParameterInfo info : infos) {
            //累计充电量
            if (info.getEsName().equals("ljcdl")) {
                ljcdl = info;
            }
            //累计放电量
            if (info.getEsName().equals("ljfdl")) {
                ljfdl = info;
            }
            //	累计节省电费
            if (info.getEsName().equals("ljjsdf")) {
                ljjsdf = info;
            }
        }
        // 存放数据
        JSONObject batteryLeftCdl = new JSONObject();
        String nameCdl = null;
        Double valueCdl = 0.0D;
        JSONObject batteryLeftFdl = new JSONObject();
        String nameFdl = null;
        Double valueFdl = 0.0D;
        JSONObject batteryLeftSdf = new JSONObject();
        String nameSdf = null;
        Double valueSdf = 0.0D;
        if (ljcdl != null && ljfdl != null && ljjsdf != null) {
            for (int i = 0; i < 12; i++) {
                Date startTime = DateBtnUtil.getFirstDayOfMonth(i);
                Date endTime = DateBtnUtil.getLastDayOfMonth(i);
                // 通过时间查询
                List<EsmsIntegration> integrationsData = integrationRepository.findByDatas(one.getIp(), startTime, endTime);
                // 通过时间筛选数据
                if (integrationsData.size() < 1) {
                    batteryLeftCdl.put(new SimpleDateFormat("yyyy-MM").format(startTime), 0.0D);
                    batteryLeftFdl.put(new SimpleDateFormat("yyyy-MM").format(startTime), 0.0D);
                    batteryLeftSdf.put(new SimpleDateFormat("yyyy-MM").format(startTime), 0.0D);
                    continue;
                } else {
                    for (EsmsIntegration ein : integrationsData) {
                        String name = new SimpleDateFormat("yyyy-MM").format(startTime);
                        if ("cdl".equals(ein.getType())) {
                            valueCdl += ein.getVal();
                            nameCdl = name;
                        }
                        if ("fdl".equals(ein.getType())) {
                            valueFdl += ein.getVal();
                            nameFdl = name;
                        }
                        if ("sdf".equals(ein.getType())) {
                            valueSdf += ein.getVal();
                            nameSdf = name;
                        }
                    }
                    batteryLeftCdl.put(nameCdl, valueCdl * 0.001D);
                    batteryLeftFdl.put(nameFdl, valueFdl * 0.001D);
                    batteryLeftSdf.put(nameSdf, valueSdf);
                    valueCdl = 0.0D;
                    valueFdl = 0.0D;
                    valueSdf = 0.0D;
                }
            }
        }
        // 返回数据
        Map<String, JSONObject> result = new HashMap<>();
        result.put("batteryLeftCdl", batteryLeftCdl);
        result.put("batteryLeftFdl", batteryLeftFdl);
        result.put("batteryLeftSdf", batteryLeftSdf);
        return result;
    }


    /**
     * 故障信息
     * 饼图
     *
     * @return
     */
    public List<JSONObject> getFaultInformation(String id) {
        // 本年的第一天  最后一天
        Date beginDayOfYear = DateBtnUtil.getBeginDayOfYear();
        Date endDayOfYear = DateBtnUtil.getEndDayOfYear();

        // 存放数据
        JSONObject faultInformation = new JSONObject();
        String oneName = "一级";
        Double oneValue = 0.0D;
        JSONObject faultInformationTwo = new JSONObject();
        String twoName = "二级";
        Double twoValue = 0.0D;
        JSONObject faultInformationThree = new JSONObject();
        String threeName = "三级";
        Double threeValue = 0.0D;
        JSONObject faultInformationFour = new JSONObject();
        String fourName = "四级";
        Double fourValue = 0.0D;
        // 通过时间查询
        for (int j = 1; j < 5; j++) {
            Double byAlarmLev = alarmRepository.findByAlarmLev(j + ".0", beginDayOfYear, endDayOfYear);
            if (j == 1) {
                oneValue = byAlarmLev;
            } else if (j == 2) {
                twoValue = byAlarmLev;
            } else if (j == 3) {
                threeValue = byAlarmLev;
            } else {
                fourValue = byAlarmLev;
            }
        }

        faultInformation.put("name", oneName);
        faultInformation.put("value", oneValue);
        faultInformationTwo.put("name", twoName);
        faultInformationTwo.put("value", twoValue);
        faultInformationThree.put("name", threeName);
        faultInformationThree.put("value", threeValue);
        faultInformationFour.put("name", fourName);
        faultInformationFour.put("value", fourValue);


        // 返回数据
        List<JSONObject> result = new ArrayList<>();
        result.add(faultInformation);
        result.add(faultInformationTwo);
        result.add(faultInformationThree);
        result.add(faultInformationFour);
        getFaultInformationDataTableSave(oneValue + twoValue + threeValue + fourValue);
        return result;
    }


    /**
     * 故障信息Table 入库
     */
    public void getFaultInformationDataTableSave(Double v) {
        reportFaultInformationRepository.deleteAll();

        List<String> dataEq = new ArrayList();
        List<String> dataSouce = new ArrayList<>();
        //获取一年的时间范围
        Date beginDayOfYear = DateBtnUtil.getBeginDayOfYear();
        Date endDayOfYear = DateBtnUtil.getEndDayOfYear();

        NumberFormat nf = NumberFormat.getNumberInstance();
        nf.setMaximumFractionDigits(2);

        // 入库
        Double allSize = v;
        String alarmLev = null;

        for (int i = 1; i < 5; i++) {
            ReportFaultInformation rfInfo = new ReportFaultInformation();
            List<Alarm> byAlarmLevData = alarmRepository.findByAlarmLevData(beginDayOfYear, endDayOfYear, i + ".0");
            List<String> alarm = getAlarm(dataEq, dataSouce, byAlarmLevData);
            String maximumEq = alarm.get(0);
            String maximunSource = alarm.get(1);
            int size = byAlarmLevData.size();
            String proportion = nf.format(Double.parseDouble(String.valueOf(size)) / allSize * 100) + "%";
            if (i == 1) {
                alarmLev = "报警等级一";
            } else if (i == 2) {
                alarmLev = "报警等级二";
            } else if (i == 3) {
                alarmLev = "报警等级三";
            } else {
                alarmLev = "报警等级四";
            }
            rfInfo.setId(UUID.randomUUID().toString().replaceAll("-", ""));
            rfInfo.setAlarmLev(alarmLev);
            rfInfo.setAlarmNum(size);
            rfInfo.setProportion(proportion);
            rfInfo.setAlarmMaximumEquipment(maximumEq);
            rfInfo.setAlarmMaximumSource(maximunSource);
            rfInfo.setCreateTime(new Date());
            rfInfo.setIsValid(1);

            reportFaultInformationRepository.save(rfInfo);
        }

    }

    /**
     * 故障信息报警数据处理
     *
     * @param dataEq
     * @param dataSouce
     * @param byAlarmLev
     * @return
     */
    private List<String> getAlarm(List<String> dataEq, List<String> dataSouce, List<Alarm> byAlarmLev) {
        List<String> list = new ArrayList<>();
        if (byAlarmLev.size() < 1) {
            list.add("");
            list.add("");
            return list;
        }
        for (Alarm alarm : byAlarmLev) {
            dataEq.add(alarm.getEquipmentName());
            dataSouce.add(alarm.getAlarmContent());
        }
        String maximumEq = getmaximumSourceAndEq(dataEq);
        String maximunSource = getmaximumSourceAndEq(dataSouce);

        list.add(maximumEq);
        list.add(maximunSource);
        return list;
    }

    /**
     * 故障信息 - 报警最多设备  报警最多内容
     *
     * @param data
     */
    private String getmaximumSourceAndEq(List<String> data) {

        if (data.size() < 1) {
            return "";
        }

        String alarmMaximumEquipmentAndSource = null;
        Map<String, Integer> map = new HashMap<>();
        for (String s : data) {
            if (map.containsKey(s)) {
                int num = map.get(s);
                map.put(s, num + 1);
            } else {
                map.put(s, 1);
            }
        }
        Integer max = Collections.max(map.values());
        for (Map.Entry<String, Integer> entry : map.entrySet()) {
            if (entry.getValue().equals(max)) {
                alarmMaximumEquipmentAndSource = entry.getKey();
            }
        }

        data.clear();
        map.clear();
        return alarmMaximumEquipmentAndSource;
    }

    /**
     * 收益表及环保贡献 入库
     *
     * @param id
     * @return
     */
    public String getTearningsData(String id) {

        reportTearingsRepository.deleteAll();

        String ip = projectManageRepository.findOne(id).getIp();

        /**
         *
         * 电网电费  (万元)   ---->累积节省电费 hdf
         * 节约电费  (万元)   sdf
         * 本年用电量 (kWh)hdf
         * 储能电费    (万元) ----> 耗电费-省电费
         */
        QEsmsIntegration qEsmsIntegration = QEsmsIntegration.esmsIntegration;
        /* 电网电费  (万元)*/
        Double hdf = integrationRepositorys.query()
                .select(qEsmsIntegration.val.sum())
                .from(qEsmsIntegration)
                .where(qEsmsIntegration.projectIp.eq(ip).and(qEsmsIntegration.type.in("hdf")))
                .groupBy(qEsmsIntegration.type)
                .fetchOne();
        if (hdf == null) {
            hdf = 0.0;
        }
        ReporTearnings tearningd = new ReporTearnings();
        tearningd.setId(UUID.randomUUID().toString().replaceAll("-", ""));
        tearningd.setName("电网电费");
        tearningd.setUnit("万元");
        tearningd.setValue(hdf);
        /* 节约电费  (万元)*/
        Double sdf = integrationRepositorys.query()
                .select(qEsmsIntegration.val.sum())
                .from(qEsmsIntegration)
                .where(qEsmsIntegration.projectIp.eq(ip).and(qEsmsIntegration.type.in("sdf")))
                .groupBy(qEsmsIntegration.type)
                .fetchOne();
        if (sdf == null) {
            sdf = 0.0;
        }
        ReporTearnings tearningjy = new ReporTearnings();
        tearningjy.setId(UUID.randomUUID().toString().replaceAll("-", ""));
        tearningjy.setName("节约电费");
        tearningjy.setUnit("万元");
        tearningjy.setValue(sdf);
        /*本年用电量 (kWh)*/
        Double saveElectricity = integrationRepositorys
                .select(qEsmsIntegration.val.sum())
                .from(qEsmsIntegration)
                .where(qEsmsIntegration.projectIp.eq(ip).and(qEsmsIntegration.type.eq("fdl")))
                .fetchOne();

        BigDecimal bigDecimal = new BigDecimal(saveElectricity / 1000.0).setScale(2, BigDecimal.ROUND_HALF_UP);
        ReporTearnings tearningfdl = new ReporTearnings();
        tearningfdl.setId(UUID.randomUUID().toString().replaceAll("-", ""));
        tearningfdl.setName("本年用电量");
        tearningfdl.setUnit("kwh");
        tearningfdl.setValue(Double.parseDouble(bigDecimal.toString()));

        ReporTearnings tearningcl = new ReporTearnings();
        tearningcl.setId(UUID.randomUUID().toString().replaceAll("-", ""));
        tearningcl.setName("储能电费");
        tearningcl.setUnit("万元");
        tearningcl.setValue(hdf-sdf);

        /* 减少排放量 (吨)
         * 减少CO2排放 (吨)
         * 等效种植树木  (颗)
         * 节约标准煤  (吨)
         */
        List<String> equipmentIds = this.getEquipmentManageIds(id);
        QDiagnoseResultInfo qDiagnoseResultInfo = QDiagnoseResultInfo.diagnoseResultInfo;
        BigDecimal saveBattery = diagnoseResultInfoRepositorys.query()
                .select(qDiagnoseResultInfo.resultVal.castToNum(BigDecimal.class).sum())
                .from(qDiagnoseResultInfo)
                .where(qDiagnoseResultInfo.equipment.id.in(equipmentIds)
                        .and(qDiagnoseResultInfo.setVal.eq("kccdl")))
                .fetchOne();
        //减少C排放
        BigDecimal jspf = saveBattery.multiply(new BigDecimal(0.0000272)).setScale(2, BigDecimal.ROUND_HALF_UP);
        ReporTearnings tearnings = new ReporTearnings();
        tearnings.setId(UUID.randomUUID().toString().replaceAll("-", ""));
        tearnings.setName("减少排放量");
        tearnings.setUnit("吨");
        tearnings.setValue(Double.parseDouble(jspf.toString()));

        //减少CO2排放
        BigDecimal jsco2 = saveBattery.multiply(new BigDecimal(0.0000997)).setScale(2, BigDecimal.ROUND_HALF_UP);
        ReporTearnings tearningc = new ReporTearnings();
        tearningc.setId(UUID.randomUUID().toString().replaceAll("-", ""));
        tearningc.setName("减少CO2排放");
        tearningc.setUnit("吨");
        tearningc.setValue(Double.parseDouble(jsco2.toString()));
        //等效种植树木
        BigDecimal zxzzsm = saveBattery.multiply(new BigDecimal(0.0000199)).setScale(2, BigDecimal.ROUND_HALF_UP);
        ReporTearnings tearningzx = new ReporTearnings();
        tearningzx.setId(UUID.randomUUID().toString().replaceAll("-", ""));
        tearningzx.setName("等效种植树木");
        tearningzx.setUnit("颗");
        tearningzx.setValue(Double.parseDouble(zxzzsm.toString()));
        //节省标准煤
        BigDecimal jsbzm = saveBattery.multiply(new BigDecimal(0.0000404)).setScale(2, BigDecimal.ROUND_HALF_UP);
        ReporTearnings tearningjs = new ReporTearnings();
        tearningjs.setId(UUID.randomUUID().toString().replaceAll("-", ""));
        tearningjs.setName("节省标准煤");
        tearningjs.setUnit("吨");
        tearningjs.setValue(Double.parseDouble(jsbzm.toString()));
        reportTearingsRepository.save(tearningc);
        reportTearingsRepository.save(tearningcl);
        reportTearingsRepository.save(tearningd);
        reportTearingsRepository.save(tearningfdl);
        reportTearingsRepository.save(tearningjs);
        reportTearingsRepository.save(tearningjy);
        reportTearingsRepository.save(tearningzx);
        reportTearingsRepository.save(tearnings);

        return "SUCCESS";
    }

    protected List<String> getEquipmentManageIds(String id) {
        QEquipmentManage qEquipmentManage = QEquipmentManage.equipmentManage;
        List<String> equipmentIds = equipmentManageRepository.query()
                .select(qEquipmentManage.id)
                .from(qEquipmentManage)
                .where(qEquipmentManage.project.id.eq(id)
                        .and(qEquipmentManage.isValid.eq(1)))
                .fetch();
        return equipmentIds;
    }

    /**
     * 查找相应汇总好的故障信息数据
     *
     * @param pageNum
     * @param request
     * @return
     */
    public PageDto getFaultInformationDataTable(int pageNum, HttpServletRequest request) {
        List<SpecificationOperator> operators = HttpRequests.getParametersStartingWith(request, "Q_");
        Sort sort = new Sort(Sort.Direction.ASC, "createTime");
        SpecificationOperator operator = new SpecificationOperator("isValid", "1", "EQ");
        operators.add(operator);
        MySpecification<ReportFaultInformation> mySpecifications = new MySpecification<>(operators);
        Pageable pageable = new PageRequest(pageNum, 4, sort);
        Page<ReportFaultInformation> page = reportFaultInformationRepository.findAll(mySpecifications, pageable);
        List<ReportFaultInformation> alarms = page.getContent();
        long total = page.getTotalElements();
        PageDto pageDto = new PageDto();
        pageDto.setRows(alarms);
        pageDto.setTotal(total);
        return pageDto;
    }

/************************************诊断信息数据统计******************************/

    /**
     * 图一 电池组容量诊断结果
     *
     * @return
     * @param id
     */
    public Map<String, JSONObject> getdignosticOne(String id) throws ParseException {
        String ip = projectManageRepository.findOne(id).getIp();
        // 存放数据
        JSONObject dignosticOne = new JSONObject();
        for (int i = 0; i < 12; i++) {
            Date startTime = DateBtnUtil.getFirstDayOfMonth(i);
            Date endTime = DateBtnUtil.getLastDayOfMonth(i);
            String dRInfo = diagnoseResultInfoRepository.findByDcbrl("dcbrl", startTime.getTime(), endTime.getTime(),ip);

            if (dRInfo == null) {
                dignosticOne.put(new SimpleDateFormat("yyyy-MM").format(startTime), 0.0D);
                continue;
            } else {
                String name = new SimpleDateFormat("yyyy-MM").format(startTime);
                dignosticOne.put(name, Double.parseDouble(dRInfo));

            }
        }
        // 返回数据
        Map<String, JSONObject> result = new HashMap<>();
        result.put("dignosticOne", dignosticOne);
        return result;

    }

    /**
     * 图二 电池组DCIR诊断结果
     *
     * @return
     * @param id
     */
    public Map<String, JSONObject> getdignosticTwo(String id) throws ParseException {
        String ip = projectManageRepository.findOne(id).getIp();
        // 存放数据
        JSONObject dignosticTwo = new JSONObject();
        for (int i = 0; i < 12; i++) {
            Date startTime = DateBtnUtil.getFirstDayOfMonth(i);
            Date endTime = DateBtnUtil.getLastDayOfMonth(i);
            String d = "dtzz\\_%";
            String dtzz = diagnoseResultInfoRepository.findDtZz(startTime.getTime(), endTime.getTime(), d,ip);
            if (dtzz == null) {
                dignosticTwo.put(new SimpleDateFormat("yyyy-MM").format(startTime), 0.0D);
                continue;
            } else {
                String name = new SimpleDateFormat("yyyy-MM").format(startTime);
                dignosticTwo.put(name, Double.parseDouble(dtzz));

            }

        }

        // 返回数据
        Map<String, JSONObject> result = new HashMap<>();
        result.put("dignosticTwo", dignosticTwo);

        return result;
    }

    /**
     * 图三 电池组循环次数
     *
     * @return
     */
    public Map<String, JSONObject> getdignosticThress() throws ParseException {
        // 存放数据
        JSONObject dignosticThress = new JSONObject();

        String sql = "SELECT a.* FROM esms_diagnose_result_info a, " +
                "(SELECT max(info.create_time) last_time, info.set_val FROM esms_diagnose_result_info info GROUP BY " +
                "set_val,date_format(info.create_time, '%Y-%m')) b WHERE a.set_val = b.set_val " +
                "AND a.create_time = b.last_time AND a.set_val = 'xhcs' ORDER BY a.create_time DESC";
        Query nativeQuery = entityManager.createNativeQuery(sql);

        nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map> resultList = nativeQuery.getResultList();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");


        for (int i = 0; i < 12; i++) {
            Date startTime = DateBtnUtil.getFirstDayOfMonth(i);
            for (Map map : resultList) {
                if (sdf.format(map.get("create_time")).equals(sdf.format(startTime))) {
                    dignosticThress.put(sdf.format(startTime), Double.parseDouble(map.get("result_val").toString()));
                    break;
                } else {
                    dignosticThress.put(new SimpleDateFormat("yyyy-MM").format(startTime), 0.0D);
                }
            }
        }
        // 返回数据
        Map<String, JSONObject> result = new HashMap<>();
        result.put("dignosticThress", dignosticThress);

        return result;
    }

    /**
     * 电池包不一致入库
     *
     * @param id
     */
    public String getPackageInconformityData(String id) throws ParseException {

        reportPackageInconformityRepository.deleteAll();
        /**
         * 总电压
         * 单体SOC
         * 单体DCIR
         * 电池容量
         * 测点温度
         */
        // 找到项目的ip
        String ip = projectManageRepository.findOne(id).getIp();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        // 总电压
        String zdy = "zdy";
        String zdyD = "总电压";
        getZdy(ip, zdy, zdyD, sdf);
        // 单体SOC
        String dtsoc = "dtsoc\\_%";
        String dtSocD = "单体SOC";
        getDtData(ip, dtsoc, dtSocD);
        // 单体阻值
        String dtzz = "dtzz\\_%";
        String dtZzD = "单体DCIR";
        getDtData(ip, dtzz, dtZzD);
        //电池容量
        String dcrl = "dcrl";
        String dcrlD = "电池容量";
        getZdy(ip, dcrl, dcrlD, sdf);
        //测点温度
        String cdwd = "cdwd";
        String cdwdD = "测点温度";
        getZdy(ip, cdwd, cdwdD, sdf);

        return "SUCCESS";

    }


    /**
     * 单体
     *
     * @param ip
     * @param dt
     * @param ip
     */
    private void getDtData(String ip, String dt, String dtD) throws ParseException {
        List<Double> datas = new ArrayList<>();
        ReportPackageInconformity inconformity = new ReportPackageInconformity();
        inconformity.setId(UUID.randomUUID().toString().replaceAll("-", ""));
        inconformity.setMonthAndName(dtD);
        for (int i = 0; i < 12; i++) {
            Date startTime = DateBtnUtil.getFirstDayOfMonth(i);
            Date lastTime = DateBtnUtil.getLastDayOfMonth(i);
            List nativeQueryDate = getNativeQueryDate(startTime, lastTime, ip, dt);
            if (nativeQueryDate.size() > 0) {
                for (int j = 0; j < nativeQueryDate.size(); j++) {
                    datas.add(Double.parseDouble(nativeQueryDate.get(i).toString()));
                }
                if (i == 0) {
                    inconformity.setMonthJan(Collections.max(datas).toString());
                } else if (i == 1) {
                    inconformity.setMonthFeb(Collections.max(datas).toString());
                } else if (i == 2) {
                    inconformity.setMonthMar(Collections.max(datas).toString());
                } else if (i == 3) {
                    inconformity.setMonthApr(Collections.max(datas).toString());
                } else if (i == 4) {
                    inconformity.setMonthMay(Collections.max(datas).toString());
                } else if (i == 5) {
                    inconformity.setMonthJun(Collections.max(datas).toString());
                } else if (i == 6) {
                    inconformity.setMonthJul(Collections.max(datas).toString());
                } else if (i == 7) {
                    inconformity.setMonthAug(Collections.max(datas).toString());
                } else if (i == 8) {
                    inconformity.setMonthSep(Collections.max(datas).toString());
                } else if (i == 9) {
                    inconformity.setMonthOct(Collections.max(datas).toString());
                } else if (i == 10) {
                    inconformity.setMonthNov(Collections.max(datas).toString());
                } else if (i == 11) {
                    inconformity.setMonthDec(Collections.max(datas).toString());

                }
                inconformity.setCreateTime(new Date());
                inconformity.setIsValid(1);
                reportPackageInconformityRepository.save(inconformity);

            } else {
                inconformity.setMonthJan("0.0");
                inconformity.setMonthFeb("0.0");
                inconformity.setMonthMar("0.0");
                inconformity.setMonthApr("0.0");
                inconformity.setMonthMay("0.0");
                inconformity.setMonthJun("0.0");
                inconformity.setMonthJul("0.0");
                inconformity.setMonthAug("0.0");
                inconformity.setMonthSep("0.0");
                inconformity.setMonthOct("0.0");
                inconformity.setMonthNov("0.0");
                inconformity.setMonthDec("0.0");
                inconformity.setCreateTime(new Date());
                inconformity.setIsValid(1);
            }
        }
        reportPackageInconformityRepository.save(inconformity);
    }

    /**
     * 电池组不一致 总电压
     *
     * @param ip
     * @param zdy
     * @param zdyD
     * @param sdf
     * @return
     * @throws ParseException
     */
    private void getZdy(String ip, String zdy, String zdyD, SimpleDateFormat sdf) throws ParseException {
        ReportPackageInconformity inconformity = new ReportPackageInconformity();
        inconformity.setId(UUID.randomUUID().toString().replaceAll("-", ""));
        inconformity.setMonthAndName(zdyD);
        List<Map> resultList = getNativeQuery(ip, zdy);
        if (resultList.size() > 0) {
            for (int i = 0; i < 12; i++) {
                Date startTime = DateBtnUtil.getFirstDayOfMonth(i);
                for (Map map : resultList) {
                    if (sdf.format(map.get("create_time")).equals(sdf.format(startTime))) {
                        if (i == 0) {
                            inconformity.setMonthJan(map.get("result_val").toString());
                            break;
                        } else if (i == 1) {
                            inconformity.setMonthFeb(map.get("result_val").toString());
                            break;
                        } else if (i == 2) {
                            inconformity.setMonthMar(map.get("result_val").toString());
                            break;
                        } else if (i == 3) {
                            inconformity.setMonthApr(map.get("result_val").toString());
                            break;
                        } else if (i == 4) {
                            inconformity.setMonthMay(map.get("result_val").toString());
                            break;
                        } else if (i == 5) {
                            inconformity.setMonthJun(map.get("result_val").toString());
                            break;
                        } else if (i == 6) {
                            inconformity.setMonthJul(map.get("result_val").toString());
                            break;
                        } else if (i == 7) {
                            inconformity.setMonthAug(map.get("result_val").toString());
                            break;
                        } else if (i == 8) {
                            inconformity.setMonthSep(map.get("result_val").toString());
                            break;
                        } else if (i == 9) {
                            inconformity.setMonthOct(map.get("result_val").toString());
                            break;
                        } else if (i == 10) {
                            inconformity.setMonthNov(map.get("result_val").toString());
                            break;
                        } else if (i == 11) {
                            inconformity.setMonthDec(map.get("result_val").toString());
                            break;
                        }

                    }
                }
            }


        } else {
            inconformity.setMonthJan("0.0");
            inconformity.setMonthFeb("0.0");
            inconformity.setMonthMar("0.0");
            inconformity.setMonthApr("0.0");
            inconformity.setMonthMay("0.0");
            inconformity.setMonthJun("0.0");
            inconformity.setMonthJul("0.0");
            inconformity.setMonthAug("0.0");
            inconformity.setMonthSep("0.0");
            inconformity.setMonthOct("0.0");
            inconformity.setMonthNov("0.0");
            inconformity.setMonthDec("0.0");
        }

        inconformity.setCreateTime(new Date());
        inconformity.setIsValid(1);
        reportPackageInconformityRepository.save(inconformity);
    }

    /**
     * 电池包不一致 zdy 查询
     *
     * @param s
     * @param ip
     * @return
     */
    private List<Map> getNativeQuery(String s, String ip) {
        String sql = "SELECT a.* FROM" +
                "(SELECT a.set_val ,a.result_val,a.create_time FROM " +
                "esms_diagnose_result_info a RIGHT OUTER JOIN esms_diagnose_batch b ON" +
                " a.batch_id=b.id AND b.ip=" + "'" + ip + "'" + ")a," +
                "(SELECT max(info.create_time) last_time, info.set_val " +
                "FROM esms_diagnose_result_info info GROUP BY set_val,date_format(info.create_time, '%Y-%m')) b " +
                "WHERE a.set_val = b.set_val AND a.create_time = b.last_time AND a.set_val =" + "'" + s + "'" +
                "ORDER BY a.create_time ASC ";

        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map> resultList = nativeQuery.getResultList();
        return resultList;
    }

    /**
     * 单体
     */
    private List getNativeQueryDate(Date startTime, Date endTime, String ip, String data) {
        String sql = "SELECT a.result_val" +
                " FROM esms_diagnose_result_info a RIGHT OUTER JOIN esms_diagnose_batch b " +
                "ON a.batch_id = b.id AND b.ip = " + "'" + ip + "'" + " WHERE a.set_val LIKE " + "'" + data + "'" +
                "AND a.create_time BETWEEN" + "'" + startTime + "'" + "AND" + "'" + endTime + "'" + "ORDER BY a.result_val DESC";

        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List resultList = nativeQuery.getResultList();
        return resultList;
    }

    /**
     * 电池组不一致
     *
     * @param pageNum
     * @param request
     * @return
     */
    public PageDto getPackageInconformity(int pageNum, HttpServletRequest request) {
        List<SpecificationOperator> operators = HttpRequests.getParametersStartingWith(request, "Q_");
        Sort sort = new Sort(Sort.Direction.ASC, "createTime");
        SpecificationOperator operator = new SpecificationOperator("isValid", "1", "EQ");
        operators.add(operator);
        MySpecification<ReportPackageInconformity> mySpecifications = new MySpecification<>(operators);
        Pageable pageable = new PageRequest(pageNum, 4, sort);
        Page<ReportPackageInconformity> page = reportPackageInconformityRepository.findAll(mySpecifications, pageable);
        List<ReportPackageInconformity> alarms = page.getContent();
        long total = page.getTotalElements();
        PageDto pageDto = new PageDto();
        pageDto.setRows(alarms);
        pageDto.setTotal(total);
        return pageDto;
    }


}
